package com.shycloud.mido.common.util;


import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.Collection;
import java.util.regex.Pattern;

/**
 * @author Xiaoxing Yu
 * @date 2020/5/29
 **/
public class DBUtil {

	static{
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");//加载数据库驱动
		}
		catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	private DBUtil() {}

	public static Connection getConn(String databaseName){
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(
					"jdbc:mysql://119.3.167." +
							"101:3306/" + databaseName + "?serverTimezone=GMT%2B8", "root","Joy@jykj123456");//连接数据库
		}
		catch(SQLException e){
			e.printStackTrace();
		}
		return conn;
	}

	public static Connection getConn(String ip, String databaseName){
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(
					"jdbc:mysql://"+ ip +":3306/" + databaseName + "?serverTimezone=GMT%2B8", "root","Joy@jykj123456");//连接数据库
		}
		catch(SQLException e){
			e.printStackTrace();
		}
		return conn;
	}

	public static void closeConn(Connection conn){
		try {
			if(conn != null ){
				conn.close();
				conn = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static Statement getStmt(Connection conn){
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stmt;
	}

	public static PreparedStatement getPStmt(Connection conn,String sql){
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pstmt;
	}

	public static void closeStmt(Statement stmt){
		try {
			if(stmt != null ){
				stmt.close();
				stmt = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public static ResultSet executeQuery(Statement stmt,String sql){
		ResultSet rs = null;
		try {
			rs = stmt.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	public static void closeRs(ResultSet rs){
		try {
			if(rs != null ){
				rs.close();
				rs = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static ResultSet executeQuery(Connection conn,String sql){//重载
		ResultSet rs = null;
		try {
			rs = conn.createStatement().executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}

	/**
	 * 转换connection查询结果为指定对象实体集合。
	 *
	 * @author chenpeng
	 * @param connection
	 * @param clazz
	 * @param sql
	 * @return
	 */
	public Collection get(final Connection connection, final Class clazz,
						  final String sql) {
		// 创建PreparedStatement
		PreparedStatement ptmt = null;
		// 创建resultset
		ResultSet rset = null;
		// 创建collection
		Collection collection = null;
		try {
			// 赋予实例
			ptmt = connection.prepareStatement(sql);
			rset = ptmt.executeQuery();
			collection = get(rset, clazz);
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		} finally {
			try {
				// 关闭rs并释放资源
				if (rset != null) {
					rset.close();
					rset = null;
				}
				// 关闭ps并释放资源
				if (ptmt != null) {
					ptmt.close();
					ptmt = null;
				}
			} catch (SQLException e) {
				System.err.println(e.getMessage());
			}
		}
		return collection;
	}

	public static Collection get(final ResultSet result, final Class clazz) {
		// 创建collection
		Collection collection = null;
		try {
			ResultSetMetaData rsmd = result.getMetaData();
			// 获得数据列数
			int cols = rsmd.getColumnCount();
			// 创建等同数据列数的arraylist类型collection实例
			collection = new ArrayList(cols);
			// 遍历结果集
			while (result.next()) {
				// 创建对象
				Object object = null;
				try {
					// 从class获得对象实体
					object = clazz.newInstance();
				} catch (Exception e) {
				}
				// 循环每条记录
				for (int i = 1; i <= cols; i++) {
					beanRegister(object, StringUtil.underlineToCamel(rsmd.getColumnName(i)), result
							.getString(i));
				}
				// 将数据插入collection
				collection.add(object);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
		} finally {

		}
		return collection;
	}

	/**
	 * 为bean自动注入数据
	 *
	 * @author chenpeng
	 * @param object
	 * @param beanProperty
	 */
	private static void beanRegister(Object object, String beanProperty, String value) {
		Object[] beanObject = beanMatch(object.getClass(), beanProperty);
		Object[] cache = new Object[1];
		Method getter = (Method) beanObject[0];
		Method setter = (Method) beanObject[1];
		try {
			// 通过get获得方法类型
			//methodType示例"java.lang.Integer"，所以这里要截下字符串取最后一个
			String methodType = getter.getReturnType().getName();

			if (methodType.equalsIgnoreCase("long")
					|| methodType.equalsIgnoreCase("java.lang.Long")) {
				if (value == null) return;
				cache[0] = new Long(value);
				setter.invoke(object, cache);
			} else if (methodType.equalsIgnoreCase("int")
					|| methodType.equalsIgnoreCase("integer")
					|| methodType.equalsIgnoreCase("java.lang.Integer")) {
				cache[0] = new Integer(value);
				setter.invoke(object, cache);
			} else if (methodType.equalsIgnoreCase("short")) {
				cache[0] = new Short(value);
				setter.invoke(object, cache);
			} else if (methodType.equalsIgnoreCase("float")) {
				cache[0] = new Float(value);
				setter.invoke(object, cache);
			} else if (methodType.equalsIgnoreCase("double")) {
				cache[0] = new Double(value);
				setter.invoke(object, cache);
			} else if (methodType.equalsIgnoreCase("boolean")) {
				cache[0] = new Boolean(value);
				setter.invoke(object, cache);
			} else if (methodType.equalsIgnoreCase("java.lang.String")
					|| methodType.equalsIgnoreCase("string")) {
				cache[0] = value;
				setter.invoke(object, cache);
			} else if (methodType.equalsIgnoreCase("java.io.InputStream")) {
			} else if (methodType.equalsIgnoreCase("char")) {
				cache[0] = (Character.valueOf(value.charAt(0)));
				setter.invoke(object, cache);
			} else if (methodType.equalsIgnoreCase("java.util.Date")
					|| methodType.equalsIgnoreCase("Date")) {
				if (value == null) return;
				if (value.equals("0000-00-00 00:00:00")) value = "2020-12-23 13:50:00";
				cache[0] = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(value);
				setter.invoke(object, cache);
			} else if (methodType.equalsIgnoreCase("java.time.LocalDateTime")
					|| methodType.equalsIgnoreCase("LocalDateTime")) {
				if (value == null) return;
				if (value.equals("0000-00-00 00:00:00")) value = "2020-12-23 13:50:00";
				cache[0] = LocalDateTime.parse(value);
				setter.invoke(object, cache);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	/**
	 * 匹配指定class中数据,并返回包含get和set方法的object
	 *
	 * @author chenpeng
	 * @param clazz
	 * @param beanProperty
	 * @return
	 */
	private static Object[] beanMatch(Class clazz, String beanProperty) {
		Object[] result = new Object[2];
		char beanPropertyChars[] = beanProperty.toCharArray();
		beanPropertyChars[0] = Character.toUpperCase(beanPropertyChars[0]);
		String s = new String(beanPropertyChars);
		String names[] = { ("set" + s).intern(), ("get" + s).intern(),
				("is" + s).intern(), ("write" + s).intern(),
				("read" + s).intern() };
		Method getter = null;
		Method setter = null;
		Method methods[] = clazz.getMethods();
		for (int i = 0; i < methods.length; i++) {
			Method method = methods[i];
			// 只取公共字段
			if (!Modifier.isPublic(method.getModifiers()))
				continue;
			String methodName = method.getName().intern();
			for (int j = 0; j < names.length; j++) {
				String name = names[j];
				if (!name.equals(methodName))
					continue;
				if (methodName.startsWith("set")
						|| methodName.startsWith("read"))
					setter = method;
				else
					getter = method;
			}
		}
		result[0] = getter;
		result[1] = setter;
		return result;
	}

	/**
	 * 生成插入语句
	 * @param tablename 表明
	 * @param clazz 与数据库中字段一一对应的类
	 * @param t 有数据的实体
	 * @param <T> 数据实体类型 如 User
	 */
	public static  <T> String getInsertSql(String tablename, Class<T> clazz, T t){
		//insert into table_name (column_name1,column_name2, ...) values (value1,value2, ...)
		String sql = "";
		Field[] fields = ReflectUtil.getFieldsDirectly(clazz, false);
		StringBuffer topHalf = new StringBuffer("insert into "+tablename+" (");
		StringBuffer afterAalf = new StringBuffer("values (");
		for (Field field : fields) {
//			if ("ID".equals(field.getName()) || "id".equals(field.getName())){
//				continue;   //id 自动生成无需手动插入
//			}

			//排除不是private修饰的属性比如说public xxx
			if (field.getModifiers() != 2) continue;

			Object fieldValue = ReflectUtil.getFieldValue(t, field.getName());

			topHalf.append( StringUtil.camelToUnderline(field.getName()) + ",");
			if (fieldValue instanceof String) {
				afterAalf.append("'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");
			} else if (fieldValue instanceof java.util.Date) {
				afterAalf.append("'" +
						new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(
								ReflectUtil.getFieldValue(t, field.getName())) + "',");
			} else {
				afterAalf.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");
			}
		}
		topHalf = new StringBuffer(StrUtil.removeSuffix(topHalf.toString(), ","));
		afterAalf = new StringBuffer(StrUtil.removeSuffix(afterAalf.toString(), ","));
		topHalf.append(") ");
		afterAalf.append(") ");
		sql = topHalf.toString() + afterAalf.toString();
		return sql;
	}

	/**
	 * 生成更新语句
	 * 必须含有id
	 * 数据实体中 null 与 空字段不参与更新
	 * @param tablename 数据库中的表明
	 * @param clazz 与数据库中字段一一对应的类
	 * @param t 有数据的实体
	 * @param <T> 数据实体类型,如 User
	 */
	public static  <T> String getUpdateSql(String tablename, Class<T> clazz, T t) {
		//UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... where ID=xxx
		//or
		//UPDATE table_name SET column_name1 = value1, column_name2 = value2, ... where id=xxx
		String sql = "";
		String id = ""; //保存id名：ID or id
		Field[] fields = ReflectUtil.getFieldsDirectly(clazz, false);
		sql = "update " + tablename + " set ";
		for (Field field : fields) {
			StringBuffer tmp = new StringBuffer();
			if ("ID".equals(field.getName()) || "id".equals(field.getName())) {
				id = field.getName();
				continue;//更新的时候无需set id=xxx
			}
			if (ReflectUtil.getFieldValue(t, field.getName()) != null && ReflectUtil.getFieldValue(t, field.getName()) != "") {
				tmp.append(field.getName() + "=");
				if (ReflectUtil.getFieldValue(t, field.getName()) instanceof String) {
					tmp.append("'" + ReflectUtil.getFieldValue(t, field.getName()) + "',");
				} else {
					tmp.append(ReflectUtil.getFieldValue(t, field.getName()) + ",");
				}
				sql += tmp;
			}
		}
		sql = StrUtil.removeSuffix(sql, ",") + " where " + id + "='" + ReflectUtil.getFieldValue(t, id) + "'";
		return sql;
	}

	/**
	 * //根据数据库生成字段 例如 private Object a;
	 * @param dbname 数据库名
	 * @param tablename 表名称
	 * @return 成员变量拼接后的字符串
	 * @throws SQLException
	 */
	public static String getPirvateObjectXxx(String dbname,String tablename) throws SQLException {
		Connection conn = getConn(dbname);
		Statement stat = conn.createStatement();
		ResultSet rs = stat.executeQuery("select COLUMN_NAME,COLUMN_TYPE from information_schema.COLUMNS where table_name = '"+tablename+"' and table_schema = '"+dbname+"'");
		StringBuffer sb=new StringBuffer();
		while (rs.next()) {
			sb.append("private Object "+rs.getObject(1)+";\n");
		}
		System.out.print(sb.toString());
		rs.close();
		stat.close();
		conn.close();
		return sb.toString();
	}

}
